﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Aspose.Cells;

namespace 导出excel
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        #region 按钮代码
        //导出数据
        private void btnTest1_Click(object sender, EventArgs e)
        {
            List<int> list = new List<int>() { 5, 18, 10, 10, 10 };
            ReportToExcel(listView1, list, "人员登记表");
            output("表格数据导出完成");
        }
        //添加数据
        private void button1_Click(object sender, EventArgs e)
        {
            listView1.Items.Clear();//清空列表数据
            addData("王喜", "女", "北京");
            addData("李梅", "女", "武汉");
            addData("宋天", "男", "广州");
            addData("唐莉", "女", "天津");
            addData("赵广", "男", "郑州");
            addData("度娘", "女", "大理");
        }
        #endregion
        #region 常用类
        //输出日志
        public void output(string log)
        {
            //如果日志信息长度超过100行,则自动清空
            if (tbxLog.GetLineFromCharIndex(tbxLog.Text.Length) > 100)
                tbxLog.Text = "";
            //添加日志
            tbxLog.AppendText(DateTime.Now.ToString("HH:mm:ss  ") + log + "\r\n");
        }
        #region 报表导出成Execl
        public void ReportToExcel(ListView list, List<int> ColumnWidth, string ReportTitleName)
        {
            //获取用户选择的excel文件名称
            string path;
            SaveFileDialog savefile = new SaveFileDialog();
            savefile.Filter = "Excel files(*.xls)|*.xls";
            if (savefile.ShowDialog() == DialogResult.OK)
            {
                //获取保存路径
                path = savefile.FileName;
                Workbook wb = new Workbook();
                Worksheet ws = wb.Worksheets[0];
                Cells cell = ws.Cells;
                //定义并获取导出的数据源
                string[,] _ReportDt = new string[list.Items.Count, list.Columns.Count];
                for (int i = 0; i < list.Items.Count; i++)
                {
                    for (int j = 0; j < list.Columns.Count; j++)
                    {
                        _ReportDt[i, j] = list.Items[i].SubItems[j].Text.ToString();
                    }
                }
                //合并第一行单元格
                Range range = cell.CreateRange(0, 0, 1, list.Columns.Count);
                range.Merge();
                cell["A1"].PutValue(ReportTitleName); //标题

                //设置行高
                cell.SetRowHeight(0, 20);

                //设置字体样式
                Style style1 = wb.Styles[wb.Styles.Add()];
                style1.HorizontalAlignment = TextAlignmentType.Center;//文字居中
                style1.Font.Name = "宋体";
                style1.Font.IsBold = true;//设置粗体
                style1.Font.Size = 12;//设置字体大小

                Style style2 = wb.Styles[wb.Styles.Add()];
                style2.HorizontalAlignment = TextAlignmentType.Center;
                style2.Font.Size = 10;

                //给单元格关联样式
                cell["A1"].SetStyle(style1); //报表名字 样式

                //设置Execl列名
                for (int i = 0; i < list.Columns.Count; i++)
                {
                    cell[1, i].PutValue(list.Columns[i].Text);
                    cell[1, i].SetStyle(style2);
                }

                //设置单元格内容
                int posStart = 2;
                for (int i = 0; i < list.Items.Count; i++)
                {
                    for (int j = 0; j < list.Columns.Count; j++)
                    {
                        cell[i + posStart, j].PutValue(_ReportDt[i, j].ToString());
                        cell[i + posStart, j].SetStyle(style2);
                    }
                }

                //设置列宽
                for (int i = 0; i < list.Columns.Count; i++)
                {
                    cell.SetColumnWidth(i, Convert.ToDouble(ColumnWidth[i].ToString()));
                }
                //保存excel表格
                wb.Save(path);
            }
        }
        //在列表中添加数据
        public void addData(string name, string sex, string add)
        {
            ListViewItem i_item = listView1.Items.Add((listView1.Items.Count + 1) + "");
            i_item.SubItems.Add(DateTime.Now.ToString());//时间
            i_item.SubItems.Add(name); //姓名
            i_item.SubItems.Add(sex);//性别
            i_item.SubItems.Add(add);//地址
        }
        #endregion
        #endregion
    }
}
